/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;
import java.util.Vector;

import Beans.NgonNgu;
import Beans.LoaiKH;

/**
 *
 * @author Venus
 */
public class LoaiKH_Model {

    public Vector LayDSLKH(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaLKH), MaNN, TenLKH, ChietKhau, SoDiem from loaikhachhang where MaNN = " + langid + " Order by MaLKH desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static int TaoMa() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(MaLKH) from loaikhachhang";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean AddLoaiKH(int MaLKH, String customertype, String mark, String reduction) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            NgonNgu[] dsnn = NgonNgu_Model.DSNgonNgu();
            String x = "insert into loaikhachhang values ";
            for (int i = 0; i < dsnn.length; i++) {
                x = x + "(" + MaLKH + ", " + dsnn[i].getMaNN() + ", '" + customertype + "' , " + reduction + ", " + mark + "),";
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector LayCustomerType(int MaLKH) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from loaikhachhang where MaLKH = " + MaLKH;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean CheckTenLKH(String tenlkh, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from nhommonan where MaNN = " + langid + " and TenLKH = '" + tenlkh + "' ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckTenLKHEdit(String malkh, String tenlkh, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from loaikhachhang where MaNN = " + langid + " and TenLKH = '" + tenlkh + "' and MaLKH != " + malkh + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean Edit(String tenlkh, String chietkhau, String sodiem, String malkh, String mann) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update loaikhachhang set TenLKH = '" + tenlkh + "' where MaLKH = " + malkh + " and MaNN = " + mann;
            String query1 = " Update loaikhachhang set SoDiem = " + sodiem + ", ChietKhau = " + chietkhau + " where MaLKH = " + malkh + " ";
            int n = stmt.executeUpdate(query);
            stmt.executeUpdate(query1);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector Search(String tenlkh, String sodiem, String chietkhau, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaLKH), MaNN, TenLKH, ChietKhau, SoDiem "
                    + " from loaikhachhang "
                    + " where MaNN = " + langid + " ";
            String[] arr = {tenlkh, sodiem, chietkhau};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenlkh)) {
                    query = query + " and TenLKH like '%" + tenlkh + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && arr[i].equals(chietkhau)) {
                    query = query + " and ChietKhau = " + chietkhau + " ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenLKH(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from loaikhachhang  where  MaNN = " + langid + " Order by TenLKH " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSoDiem(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from loaikhachhang  where  MaNN = " + langid + " Order by SoDiem " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortChietKhau(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from loaikhachhang  where  MaNN = " + langid + " Order by ChietKhau " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenLKH(String tenlkh, String sodiem, String chietkhau, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaLKH), MaNN, TenLKH, ChietKhau, SoDiem "
                    + " from loaikhachhang "
                    + " where MaNN = " + langid + " ";
            String[] arr = {tenlkh, sodiem, chietkhau};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenlkh)) {
                    query = query + " and TenLKH like '%" + tenlkh + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && arr[i].equals(chietkhau)) {
                    query = query + " and ChietKhau = " + chietkhau + " ";
                }
            }
            query = query + " Order by TenLKH " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchSoDiem(String tenlkh, String sodiem, String chietkhau, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaLKH), MaNN, TenLKH, ChietKhau, SoDiem "
                    + " from loaikhachhang "
                    + " where MaNN = " + langid + " ";
            String[] arr = {tenlkh, sodiem, chietkhau};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenlkh)) {
                    query = query + " and TenLKH like '%" + tenlkh + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && arr[i].equals(chietkhau)) {
                    query = query + " and ChietKhau = " + chietkhau + " ";
                }
            }
            query = query + " Order by SoDiem " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchChietKhau(String tenlkh, String sodiem, String chietkhau, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaLKH), MaNN, TenLKH, ChietKhau, SoDiem "
                    + " from loaikhachhang "
                    + " where MaNN = " + langid + " ";
            String[] arr = {tenlkh, sodiem, chietkhau};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenlkh)) {
                    query = query + " and TenLKH like '%" + tenlkh + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && arr[i].equals(chietkhau)) {
                    query = query + " and ChietKhau = " + chietkhau + " ";
                }
            }
            query = query + " Order by ChietKhau " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKH lkh = new Beans.LoaiKH();
                lkh.setMaLKH(rss.getByte(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                v.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static LoaiKH[] DSLoaiKH(int malkh, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector dsnn = new Vector();// biáº¿n dsnn lÆ°u danh sÃ¡ch ngÃ´n ngá»¯ hiá»‡n cÃ³.
        String query = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            query = "Select * from loaikhachhang where MaLKH != " + malkh + " and MaNN = " + langid;
            rss = stmt.executeQuery(query);
            //rss = ConnectionPool.Select(query);
            while (rss.next()) {
                LoaiKH lkh = new LoaiKH();
                lkh.setMaLKH(rss.getInt(1));
                lkh.setMaNN(rss.getInt(2));
                lkh.setTenLKH(rss.getString(3));
                lkh.setChietKhau(rss.getInt(4));
                lkh.setSoDiem(rss.getInt(5));
                dsnn.add(lkh);
            }
            stmt.close();
            rss.close();
            conn.close();
            return convertToArray(dsnn);
        } catch (Exception e) {
            e.printStackTrace();

            return null;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static LoaiKH[] convertToArray(Vector v) {
        int n = v.size();
        LoaiKH[] arr = new LoaiKH[n];
        for (int i = 0; i < n; i++) {
            arr[i] = (LoaiKH) v.get(i);
        }
        return arr;
    }
}
